package org.ttitfly.workflow.engine.persistence.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.sql.DataSource;

/**
 * JDBC操作模板
 * 
 * @author 潘海春
 * @version 2009-9-18
 */
public class JdbcTemplate {

	/** logger */
	protected Logger logger = Logger.getLogger(JdbcTemplate.class.getName());

	/**
	 * 数据源
	 */
	private DataSource dataSource;

	/**
	 * 默认参数设置器
	 */
	private ParameterSetter parameterSetter = new ParameterSetter() {

		@Override
		public void applyParameters(PreparedStatement pstmt, Object... params)
				throws SQLException {
			if (params == null || params.length == 0) {
				return;
			}
			int i = 1;
			for (Object param : params) {
				if (param instanceof String) {
					pstmt.setString(i++, (String) param);
					continue;
				}
				if (param instanceof Integer) {
					pstmt.setInt(i++, (Integer) param);
					continue;
				}
				if (param instanceof Date) {
					pstmt.setDate(i++, new java.sql.Date(((Date) param).getTime()));
					continue;
				}
				if (param instanceof Float) {
					pstmt.setFloat(i++, (Float) param);
					continue;
				}
				if (param instanceof Boolean) {
					pstmt.setInt(i++, ((Boolean) param) ? 1 : 0);
					continue;
				}
				pstmt.setObject(i++, param);
			}
		}
	};

	/**
	 * 构造函数
	 * 
	 * @param dataSource
	 */
	public JdbcTemplate(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * 构造函数
	 * 
	 * @param dataSource
	 * @param parameterSetter
	 */
	public JdbcTemplate(DataSource dataSource, ParameterSetter parameterSetter) {
		this.dataSource = dataSource;
		this.parameterSetter = parameterSetter;
	}

	/**
	 * 执行回调接口
	 * 
	 * @param action
	 *            回调接口
	 * @return
	 * @throws JdbcException
	 */
	public Object execute(ConnectionCallback action) throws JdbcException {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			return action.doInConnection(connection);
		} catch (SQLException e) {
			throw new JdbcException(e);
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				throw new JdbcException(e);
			}
		}

	}

	/**
	 * 执行sql语句批量更新
	 * 
	 * @param sql
	 * @param params
	 *            参数
	 * @return
	 * @throws JdbcException
	 */
	public int executeBatchUpdate(final String sql, final List<Object[]> params)
			throws JdbcException {
		return (Integer) execute(new ConnectionCallback() {
			@Override
			public Object doInConnection(Connection connection) throws SQLException {
				logSql(sql);
				PreparedStatement pstmt = connection.prepareStatement(sql);
				int rowCount = 0;
				if (params != null && (!params.isEmpty())) {
					for (Object[] paramArray : params) {
						logParams(paramArray);
						parameterSetter.applyParameters(pstmt, paramArray);
						pstmt.addBatch();
					}
					int[] rows = pstmt.executeBatch();
					for (int count : rows) {
						rowCount += count;
					}
				} else {
					rowCount = pstmt.executeUpdate();
				}
				pstmt.close();
				return rowCount;
			}

		});
	}

	/**
	 * 执行插入语句，返回主健
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws JdbcException
	 */
	public Object executeInsert(final String sql, final Object... params)
			throws JdbcException {
		return execute(new ConnectionCallback() {
			@Override
			public Object doInConnection(Connection connection) throws SQLException {
				logSql(sql);
				PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				logParams(params);
				parameterSetter.applyParameters(pstmt, params);
				pstmt.execute();
				ResultSet rs = pstmt.getGeneratedKeys();
				Object key = rs.next() ? rs.getObject(1) : null;
				rs.close();
				return key;
			}
		});
	}

	/**
	 * 执行sql更新语句
	 * 
	 * @param sql
	 * @return
	 * @throws JdbcException
	 */
	public int executeUpdate(String sql) throws JdbcException {
		return executeUpdate(sql, new Object[0]);
	}

	/**
	 * 执行sql更新语句
	 * 
	 * @param sql
	 * @param params
	 *            参数
	 * @return
	 * @throws JdbcException
	 */
	public int executeUpdate(final String sql, final Object... params)
			throws JdbcException {
		return (Integer) execute(new ConnectionCallback() {
			@Override
			public Object doInConnection(Connection connection) throws SQLException {
				logSql(sql);
				PreparedStatement pstmt = connection.prepareStatement(sql);
				logParams(params);
				parameterSetter.applyParameters(pstmt, params);
				int rowCount = pstmt.executeUpdate();
				pstmt.close();
				return rowCount;
			}
		});
	}

	/**
	 * 执行sql查询语句返回集合
	 * 
	 * @param sql
	 * @return
	 * @throws JdbcException
	 */
	public List<Object> queryForList(String sql) throws JdbcException {
		return queryForList(sql, new ObjectAutoRowMapper());
	}

	/**
	 * 执行sql查询语句返回集合
	 * 
	 * @param <T>
	 * @param sql
	 * @param rowMapper
	 *            行映射器
	 * @return
	 * @throws JdbcException
	 */
	public <T> List<T> queryForList(String sql, RowMapper<T> rowMapper)
			throws JdbcException {
		return queryForList(sql, rowMapper, new Object[0]);
	}

	/**
	 * 执行sql语句查询单个对象
	 * 
	 * @param sql
	 * @param params
	 *            参数
	 * @return
	 * @throws JdbcException
	 */
	public List<Object> queryForList(String sql, Object... params)
			throws JdbcException {
		return queryForList(sql, new ObjectAutoRowMapper(), params);
	}

	/**
	 * 执行sql语句查询单个对象
	 * 
	 * @param <T>
	 * @param sql
	 * @param rowMapper
	 *            行映射器
	 * @param params
	 *            参数
	 * @return
	 * @throws JdbcException
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryForList(final String sql,
			final RowMapper<T> rowMapper, final Object... params)
			throws JdbcException {
		return (List<T>) execute(new ConnectionCallback() {
			@Override
			public Object doInConnection(Connection connection) throws SQLException {
				logSql(sql);
				PreparedStatement pstmt = connection.prepareStatement(sql);
				parameterSetter.applyParameters(pstmt, params);
				logParams(params);
				ResultSet rs = pstmt.executeQuery();
				List<T> results = new ArrayList<T>();
				while (rs.next()) {
					results.add(rowMapper.mapRow(rs));
				}
				rs.close();
				pstmt.close();
				return results;
			}

		});
	}

	/**
	 * 执行sql语句查询单个对象
	 * 
	 * @param sql
	 * @return
	 * @throws JdbcException
	 */
	public Object queryForObject(String sql) throws JdbcException {
		return queryForObject(sql, new ObjectAutoRowMapper(), new Object[0]);
	}

	/**
	 * 执行sql语句返回单个对象
	 * 
	 * @param sql
	 * @param rowMapper
	 *            行映射器
	 * @return
	 * @throws JdbcException
	 */
	public <T> T queryForObject(String sql, RowMapper<T> rowMapper)
			throws JdbcException {
		return queryForObject(sql, rowMapper, new Object[0]);
	}

	/**
	 * 执行sql语句查询单个对象
	 * 
	 * @param sql
	 * @param params
	 *            参数
	 * @return
	 * @throws JdbcException
	 */
	public Object queryForObject(String sql, Object... params)
			throws JdbcException {
		return queryForObject(sql, new ObjectAutoRowMapper(), params);
	}

	/**
	 * 执行sql语句查询单个对象
	 * 
	 * @param sql
	 * @param rowMapper
	 *            行映射器
	 * @param params
	 *            参数
	 * @return
	 * @throws JdbcException
	 */
	public <T> T queryForObject(String sql, RowMapper<T> rowMapper,
			Object... params) throws JdbcException {
		List<T> results = queryForList(sql, rowMapper, params);
		if (!results.isEmpty()) {
			return results.get(0);
		}
		return null;
	}

	/**
	 * 对象自动行映射
	 * 
	 * @author 潘海春
	 * @version 2010-3-19
	 */
	public static class ObjectAutoRowMapper implements RowMapper<Object> {

		@Override
		public Object mapRow(ResultSet resultSet) throws SQLException {
			int columnCount = resultSet.getMetaData().getColumnCount();
			if (columnCount == 1) {
				return resultSet.getObject(1);
			}
			Object[] result = new Object[columnCount];
			for (int i = 0; i < columnCount; i++) {
				result[i] = resultSet.getObject(i + 1);
			}
			return result;
		}

	}

	/**
	 * map行映射
	 * 
	 * @author 潘海春
	 * @version 2010-3-19
	 */
	public static class MapRowMapper implements RowMapper<Map<String, Object>> {

		@Override
		public Map<String, Object> mapRow(ResultSet resultSet)
				throws SQLException {
			ResultSetMetaData rsmd = resultSet.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Map<String, Object> result = new HashMap<String, Object>();
			for (int i = 0; i < columnCount; i++) {
				result.put(rsmd.getColumnName(i + 1), resultSet.getObject(i + 1));
			}
			return result;
		}
	}

	/**
	 * 打印sql及参数
	 * 
	 * @param sql
	 */
	protected void logSql(String sql) {
		StringBuilder sb = new StringBuilder("SQL:");
		sb.append(sql);
		logger.info(sb.toString());
	}

	/**
	 * 打印sql及参数
	 * 
	 * @param sql
	 * @param params
	 */
	protected void logParams(Object[] params) {
		StringBuilder sb = new StringBuilder("params:[");
		if (params != null && params.length > 0) {
			for (Object param : params) {
				if (param instanceof Date) {
					sb.append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) param)).append(",");
				} else {
					sb.append(param).append(",");
				}
			}
			sb.deleteCharAt(sb.length() - 1);
		}
		sb.append("]");
		logger.info(sb.toString());
	}
}